﻿// =================================================================== 
// 项目说明
//====================================================================

// 负责人：
// ===================================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using CommonLib.Model;
using CommonLib.IDAL;
using System.Data.SqlClient;

namespace MSSQLDAL
{
    public class CommLibStoreProcedureDAL : ICommLibStoreProcedureDAL
    {		
      #region 变量定义
        public const string para_GETNEWIDFORCODE = "@MAT_NO";
		public const string para_FNYX_GETPRICEINFO = "@VOLTAGEID,@PRICEKINDID";
		public const string para_GETNEWIDFORMAT = "@T_NAME,@T_TABLE,@T_FIELD,@C_TIME,@FORMAT";
		public const string para_GETNEWIDFORTABLE = "@T_NAME,@T_TABLE,@T_FIELD";
		public const string para_PROC_GETNEWIDFORTABLEBATCH = "@TABNAME,@T_FIELD,@ADDCOUNT,@NEWID_VAR";
		#endregion
		
		public CommLibStoreProcedureDAL()
		{
		}
		
		#region 无事务


        public Int32 GETNEWIDFORCODE(string _MAT_NO)
        {
            int ParamCount = 2;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.Int;

            _param[1] = new SqlParameter("@MAT_NO", _MAT_NO);

            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "GetNewIdForCode", _param);
            Int32 ReurnStr = Int32.Parse(_param[0].Value.ToString());
            return ReurnStr;
        }

        public Int32 LeadBidder(string _Bidder_ID)
        {
            int ParamCount = 2;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.Int;

            _param[1] = new SqlParameter("@Bidder_ID", _Bidder_ID);

            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "LeadBidderRecords", _param);
            Int32 ReurnStr = Int32.Parse(_param[0].Value.ToString());
            return ReurnStr;
        }

		public string FNYX_GETPRICEINFO(string OLTAGEID,string RICEKINDID)
		{	
			int ParamCount=3;
			SqlParameter[] _param = new SqlParameter[ParamCount];
			_param[0]=new SqlParameter();
			_param[0].ParameterName="@RETURN";
			_param[0].Direction = ParameterDirection.ReturnValue;
			_param[0].SqlDbType =SqlDbType.VarChar;
			
		    _param[1] = new SqlParameter("@VOLTAGEID",OLTAGEID);			
		    _param[2] = new SqlParameter("@PRICEKINDID",RICEKINDID);			
			SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "FNYX_GETPRICEINFO", _param);

			string ReurnStr=_param[0].Value.ToString();  	
			return ReurnStr;
		}
		
		public Int32 GETNEWIDFORMAT(string _NAME,string _TABLE,string _FIELD,string ORMAT)
		{	
			int ParamCount=5;
			SqlParameter[] _param = new SqlParameter[ParamCount];
			_param[0]=new SqlParameter();
			_param[0].ParameterName="@RETURN";
			_param[0].Direction = ParameterDirection.ReturnValue;
			_param[0].SqlDbType =SqlDbType.Int;
			
		    _param[1] = new SqlParameter("@T_NAME",_NAME);			
		    _param[2] = new SqlParameter("@T_TABLE",_TABLE);			
		    _param[3] = new SqlParameter("@T_FIELD",_FIELD);
            _param[4] = new SqlParameter("@CUR_FORMAT", ORMAT);			
			SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "GETNEWIDFORMAT", _param);
			Int32 ReurnStr=Int32.Parse(_param[0].Value.ToString());  	
			return ReurnStr;
		}
		
		public Int32 GETNEWIDFORTABLE(string _NAME,string _TABLE,string _FIELD)
		{	
			int ParamCount=4;
			SqlParameter[] _param = new SqlParameter[ParamCount];
			_param[0]=new SqlParameter();
			_param[0].ParameterName="@RETURN";
			_param[0].Direction = ParameterDirection.ReturnValue;
			_param[0].SqlDbType =SqlDbType.Int;
			
		    _param[1] = new SqlParameter("@T_NAME",_NAME);			
		    _param[2] = new SqlParameter("@T_TABLE",_TABLE);			
		    _param[3] = new SqlParameter("@T_FIELD",_FIELD);			
			SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "GETNEWIDFORTABLE", _param);
			Int32 ReurnStr=Int32.Parse(_param[0].Value.ToString());  	
			return ReurnStr;
		}
		
		public void PROC_GETNEWIDFORTABLEBATCH(string ABNAME,string _FIELD,Int32 DDCOUNT,ref Int32 EWID_VAR)
		{	
			int ParamCount=4;
			SqlParameter[] _param = new SqlParameter[ParamCount];
		    _param[0] = new SqlParameter("@TABNAME",ABNAME);			
		    _param[1] = new SqlParameter("@T_FIELD",_FIELD);			
		    _param[2] = new SqlParameter("@ADDCOUNT",DDCOUNT);			
		    _param[3] = new SqlParameter("@NEWID_VAR",EWID_VAR);			
			_param[3].Direction = ParameterDirection.Output;
			SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "PROC_GETNEWIDFORTABLEBATCH", _param);
			EWID_VAR=(Int32)_param[3].Value;
		}

        public Int32 PROC_GETNEWSNFORTABLE2(string _NAME, string _TABLE, string _FIELD, string ORMAT)
        {
            int ParamCount = 5;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.Int;

            _param[1] = new SqlParameter("@T_NAME", _NAME);
            _param[2] = new SqlParameter("@T_TABLE", _TABLE);
            _param[3] = new SqlParameter("@T_FIELD", _FIELD);
            _param[4] = new SqlParameter("@CUR_FORMAT", ORMAT);
            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "GetNewSNForTable2", _param);
            Int32 ReurnStr = Int32.Parse(_param[0].Value.ToString());
            return ReurnStr;
        }

        public Int32 PROC_CREATEFEEMONTHINOUTRPT(string _STOREROOMID, string _FEEMONTH, string _START_TIME, string _END_TIME, string _CREATOR)
        {
            int ParamCount = 6;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.Int;

            _param[1] = new SqlParameter("@DEPT_INDEX", _STOREROOMID);
            _param[2] = new SqlParameter("@FEEMONTH", _FEEMONTH);
            _param[3] = new SqlParameter("@START_TIME", _START_TIME);
            _param[4] = new SqlParameter("@END_TIME", _END_TIME);
            _param[5] = new SqlParameter("@CREATOR", _CREATOR);
            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "CREATEFEEMONTHINOUTRPT", _param);
            Int32 ReurnStr = Int32.Parse(_param[0].Value.ToString());
            return ReurnStr;
        }

        public Int32 PROC_CreateFeeMonthUseRpt(string _STOREROOMID, string _FEEMONTH, string _START_TIME, string _END_TIME, string _CREATOR, string _MAIN_DEPT)
        {
            int ParamCount = 7;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.Int;

            _param[1] = new SqlParameter("@DEPT_INDEX", _STOREROOMID);
            _param[2] = new SqlParameter("@FEEMONTH", _FEEMONTH);
            _param[3] = new SqlParameter("@START_TIME", _START_TIME);
            _param[4] = new SqlParameter("@END_TIME", _END_TIME);
            _param[5] = new SqlParameter("@CREATOR", _CREATOR);
            _param[6] = new SqlParameter("@MAIN_DEPT_INDEX", _MAIN_DEPT);
            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "CreateFeeMonthUseRpt", _param);
            Int32 ReurnStr = Int32.Parse(_param[0].Value.ToString());
            return ReurnStr;
        }

        public string RunProcess(string Condations, int FeePay_id, string FEEPAY_NO, string apply_user, decimal feepay_money, string feepay_note)
        {
            int ParamCount = 7;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.VarChar;
            _param[1] = new SqlParameter("@Condations", Condations);
            _param[2] = new SqlParameter("@FeePay_id", FeePay_id);
            _param[3] = new SqlParameter("@FEEPAY_NO", FEEPAY_NO);
            _param[4] = new SqlParameter("@apply_user", apply_user);
            _param[5] = new SqlParameter("@feepay_money", feepay_money);
            _param[6] = new SqlParameter("@feepay_note", feepay_note);
            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "FeePayEdit", _param);

            string ReurnStr = _param[0].Value.ToString();
            return ReurnStr;
        }

        public string DeleteFeepay(int FeePay_id)
        {
            int ParamCount = 2;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.VarChar;
            _param[1] = new SqlParameter("@FeePay_id", FeePay_id);
            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "DeleteFeepay", _param);

            string ReurnStr = _param[0].Value.ToString();
            return ReurnStr;
        }

        public int CreateDeptCheckMonthRptByMode(int storeroom_id, string checkmonth, string start_time, string end_time, string creator)
        {
            int ParamCount = 6;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.Int;
            _param[1] = new SqlParameter("@storeroom_id", storeroom_id);
            _param[2] = new SqlParameter("@checkmonth", checkmonth);
            _param[3] = new SqlParameter("@start_time", start_time);
            _param[4] = new SqlParameter("@end_time", end_time);
            _param[5] = new SqlParameter("@creator", creator);
            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "CreateDeptCheckMonthRptByMode", _param);

            int ReurnStr = int.Parse(_param[0].Value.ToString());
            return ReurnStr;
        }

        public DataSet ExecPro(DataTable dt,string Pname)
        {
            DataSet ds = new DataSet();
            int ParamCount = dt.Rows.Count;
            if (ParamCount != 0)
            {
                SqlParameter[] _param = new SqlParameter[ParamCount+1];
                _param[0] = new SqlParameter();
                _param[0].ParameterName = "@RETURN";
                _param[0].Direction = ParameterDirection.ReturnValue;
                _param[0].SqlDbType = SqlDbType.VarChar;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    _param[i + 1] = new SqlParameter("@" + dt.Rows[i]["NAME"].ToString() + "", dt.Rows[i]["VALUE"].ToString());
                }
                ds = SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.StoredProcedure, Pname, _param);
            }
            else
            {
                ds = SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.StoredProcedure, Pname);
            }
            return ds;
        }

		#endregion
		
		#region 无事务


		public string FNYX_GETPRICEINFO(DBTransaction db_trans,string OLTAGEID,string RICEKINDID)
		{	
			int ParamCount=3;
			SqlParameter[] _param = new SqlParameter[ParamCount];
			_param[0]=new SqlParameter();
			_param[0].ParameterName="@RETURN";
			_param[0].Direction = ParameterDirection.ReturnValue;
			_param[0].SqlDbType =SqlDbType.VarChar;
		    _param[1] = new SqlParameter("@VOLTAGEID",OLTAGEID);			
		    _param[2] = new SqlParameter("@PRICEKINDID",RICEKINDID);			
			SqlHelper.ExecuteScalar(db_trans.sql_trans, CommandType.StoredProcedure, "FNYX_GETPRICEINFO", _param);

			string ReurnStr=_param[0].Value.ToString();  	
			return ReurnStr;
		}
		
		public Int32 GETNEWIDFORMAT(DBTransaction db_trans,string _NAME,string _TABLE,string _FIELD,string ORMAT)
		{	
			int ParamCount=5;
			SqlParameter[] _param = new SqlParameter[ParamCount];
			_param[0]=new SqlParameter();
			_param[0].ParameterName="@RETURN";
			_param[0].Direction = ParameterDirection.ReturnValue;
			_param[0].SqlDbType =SqlDbType.Int;
		    _param[1] = new SqlParameter("@T_NAME",_NAME);			
		    _param[2] = new SqlParameter("@T_TABLE",_TABLE);			
		    _param[3] = new SqlParameter("@T_FIELD",_FIELD);
            _param[4] = new SqlParameter("@CUR_FORMAT", ORMAT);			
			SqlHelper.ExecuteScalar(db_trans.sql_trans, CommandType.StoredProcedure, "GETNEWIDFORMAT", _param);
			Int32 ReurnStr=Int32.Parse(_param[0].Value.ToString());  	
			return ReurnStr;
		}
		
		public Int32 GETNEWIDFORTABLE(DBTransaction db_trans,string _NAME,string _TABLE,string _FIELD)
		{	
			int ParamCount=4;
			SqlParameter[] _param = new SqlParameter[ParamCount];
			_param[0]=new SqlParameter();
			_param[0].ParameterName="@RETURN";
			_param[0].Direction = ParameterDirection.ReturnValue;
			_param[0].SqlDbType =SqlDbType.Int;
		    _param[1] = new SqlParameter("@T_NAME",_NAME);			
		    _param[2] = new SqlParameter("@T_TABLE",_TABLE);			
		    _param[3] = new SqlParameter("@T_FIELD",_FIELD);			
			SqlHelper.ExecuteScalar(db_trans.sql_trans, CommandType.StoredProcedure, "GETNEWIDFORTABLE", _param);
			Int32 ReurnStr=Int32.Parse(_param[0].Value.ToString());  	
			return ReurnStr;
		}
		
		public void PROC_GETNEWIDFORTABLEBATCH(DBTransaction db_trans,string ABNAME,string _FIELD,Int32 DDCOUNT,ref Int32 EWID_VAR)
		{	
			int ParamCount=4;
			SqlParameter[] _param = new SqlParameter[ParamCount];
		    _param[0] = new SqlParameter("@TABNAME",ABNAME);			
		    _param[1] = new SqlParameter("@T_FIELD",_FIELD);			
		    _param[2] = new SqlParameter("@ADDCOUNT",DDCOUNT);			
		    _param[3] = new SqlParameter("@NEWID_VAR",EWID_VAR);			
			_param[3].Direction = ParameterDirection.Output;
			SqlHelper.ExecuteScalar(db_trans.sql_trans, CommandType.StoredProcedure, "PROC_GETNEWIDFORTABLEBATCH", _param);
			EWID_VAR=(Int32)_param[3].Value;
		}

        public string GetNewSNForTableWithNewid(string Charge_No, int Begin_Id)
        {
            int ParamCount = 6;
            SqlParameter[] _param = new SqlParameter[ParamCount];
            _param[0] = new SqlParameter();
            _param[0].ParameterName = "@RETURN";
            _param[0].Direction = ParameterDirection.ReturnValue;
            _param[0].SqlDbType = SqlDbType.VarChar;
            _param[1] = new SqlParameter("@T_NAME", Charge_No);
            _param[2] = new SqlParameter("@T_TABLE", Charge_No);
            _param[3] = new SqlParameter("@T_FIELD", Charge_No);
            _param[4] = new SqlParameter("@CUR_FORMAT", "");
            _param[5] = new SqlParameter("@START_ID", Begin_Id);
            SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.StoredProcedure, "GetNewSNForTableWithNewid", _param);
            string  ReurnStr = _param[0].Value.ToString();
            return ReurnStr;
        }


		
		#endregion
    }
		
}
  


